package test01.demo03;


import test01.demo02.userOperation.tools.Const;

import java.sql.*;
import java.util.*;

/**
 * 自己尝试对，插入，修改，删除，查询方法做一下封装；
 * 提示，写一个类，比如UserDao,这个类中有四个方法
 * void insert(int userid,String userpwd,String loginname);
 * void update(int userid,String userid,String password,String name);
 * void delete(int userid)
 * List select();
 */
public class UserDao {

        //加载数据库驱动
    static{
        try {
            Class.forName(Const.DRIVER);
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

    /**
     * 增加
     * @param userid
     * @param userpwd
     * @param loginname
     */
    public void insert(int userid,String userpwd,String loginname,String realname){

        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
                //获取数据库连接
                conn = DriverManager.getConnection(
                        Const.URL_TEST,Const.USERNAME,Const.PASSWORD
                );

                //创建statement对象
                ps = conn.prepareStatement(
                        "insert into T_Users(userid,userpwd,loginname,realname) values(?,?,?,?)");

                ps.setInt(1,userid);
                ps.setString(2,userpwd);
                ps.setString(3,loginname);
                ps.setString(4,realname);

                result = ps.executeUpdate();
                if (result>0){
                    System.out.println("添加成功");
                }else {
                    System.out.println("添加失败");
                }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }
    }

    /**
     * 更新修改
     * @param userid
     * @param loginname 登录名
     * @param userpwd 密码
     * @param realname 真实姓名
     */
    public void update(int userid,String loginname,String userpwd,String realname){

        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );
            //创建statement对象
            ps = conn.prepareStatement(
                    "update T_Users set loginname=?, userpwd=?, realname=? where userid=?");
            ps.setString(1,loginname);
            ps.setString(2,userpwd);
            ps.setString(3,realname);
            ps.setInt(4,userid);

            result = ps.executeUpdate();
            if (result>0){
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }
    }

    /**
     * 更新修改loginname
     * @param loginname
     */
    public void update(String userpwd,String loginname){

        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );
            //创建statement对象
            ps = conn.prepareStatement(
                    "update T_Users set userpwd=? where loginname=?");
            ps.setString(1,userpwd);
            ps.setString(2,loginname);

            result = ps.executeUpdate();
            if (result>0){
                System.out.println("修改成功");
            } else {
                System.out.println("修改失败");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }
    }



    /**
     * 查询操作
     * @return
     */
    public List<Map<String,Object>> select(int userid){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );
            //创建statement对象
            ps = conn.prepareStatement(
                    "select * from T_Users where userid = ?");
            ps.setInt(1,userid);
            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    //getObject()是ResultSet接口中的一个方法，意思是从结果中获取指定列的值
                    map.put(rsmd.getColumnName(i),rs.getObject(i));
                }
                list.add(map);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps,rs);
        }
        return list;
    }

    /**
     * 查询所有结果操作
     * @return
     */
    public void select(){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        List<Map<String, Object>> list = new ArrayList<>();

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );
            //创建statement对象
            ps = conn.prepareStatement(
                    "select * from T_Users");

            rs = ps.executeQuery();
            ResultSetMetaData rsmd = rs.getMetaData();

            while(rs.next()){
                Map<String,Object> map = new HashMap<>();
                for (int i = 1; i <= rsmd.getColumnCount(); i++) {
                    //getObject()是ResultSet接口中的一个方法，意思是从结果中获取指定列的值
                    map.put(rsmd.getColumnName(i),rs.getObject(i));
                }
                list.add(map);
            }

            //遍历输出结果集中的数据
            for (Map<String, Object> stringObjectMap : list) {
                for (Map.Entry<String, Object> stringObjectEntry : stringObjectMap.entrySet()) {
                    System.out.println(stringObjectEntry.getKey()+"===="+stringObjectEntry.getValue());
                }
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps,rs);
        }
    }


    /**
     * 根据LoginName查询结果它的userid
     * @return
     */
    public int select(String loginname){
        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet rs = null;
        int resultUserId = 0;

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );
            //创建statement对象
            ps = conn.prepareStatement(
                    "select userid from T_Users where loginname = ?");
            ps.setString(1,loginname);
            rs = ps.executeQuery();

            if (rs.next()) {
                // 从结果集中获取 userid 的值
                resultUserId = rs.getInt("userid");
            }

        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps,rs);
        }
        return resultUserId;
    }

    /**
     * 删除操作
     * @param userid
     */
    public void delete(int userid){

        Connection conn = null;
        PreparedStatement ps = null;
        int result = 0;
        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );

            //创建statement对象
            ps = conn.prepareStatement(
                    "delete from T_Users where userid = ?");

            ps.setInt(1,userid);

            result = ps.executeUpdate();
            if (result>0){
                System.out.println("删除成功");
            }else {
                System.out.println("删除失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }

    }

    /**
     * 登录方法
     */
    public void login(int userid,String userpwd){

        Connection conn = null;
        PreparedStatement ps = null;
        ResultSet result = null;

        try {
            //获取数据库连接
            conn = DriverManager.getConnection(
                    Const.URL_TEST,Const.USERNAME,Const.PASSWORD
            );

            //创建statement对象
            ps = conn.prepareStatement(
                    "select * from T_users where userid = ? and userpwd = ?");

            ps.setInt(1,userid);
            ps.setString(2,userpwd);


            result = ps.executeQuery();
            if (result.next()){
                System.out.println("登录成功");
                select();
            }else {
                System.out.println("登录失败");
            }


        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            ConnectionTool.closed(conn,ps);
        }

    }


    /**
     * 完成jdbc的操作；
     * 1.添加两个用户,分别是
     * liubei,liubei,‘刘备’；
     * guanyu,guanyu,关宇；
     * 2.修改；
     * 修改guanyu的密码，改成tiger;
     * 修改userid是2的用户，改成andy,tiger,‘安迪’；
     * 3.删除
     * 删除userid是3的用户；
     * 4.查询
     * 查询出所有的用户，并将信息打印出来
     * 5.登录
     * 要求用户输入用户名和密码，判断是否登录成功，如果成功，则显示所有的用户信息，不成功，则提示"用户名或密码错误",使用Scanner来接收用户名和密码；
     * @param args
     */
    public static void main(String[] args) {
        UserDao ud = new UserDao();
//        ud.insert(2,"liubei","liubei","刘备");
//        ud.insert(3,"guanyu","guanyu","关宇");
//        System.out.println("请输入你想修改的loginname：");
//        Scanner scanner = new Scanner(System.in);
//        String wantLoginName = scanner.nextLine();
//        if (ud.select(1)==null){
//            System.out.println("该用户不存在");
//        }else {
//            System.out.println("请输入你想修改的密码：");
//            String wantpwd = scanner.nextLine();
//            ud.update(wantpwd,wantLoginName);
//        }

//        System.out.println("请输入你想修改的userid：");
//        Scanner scanner = new Scanner(System.in);
//        int wantuserid = scanner.nextInt();
//        scanner.nextLine();
//        if (ud.select(wantuserid)==null){
//            System.out.println("该用户不存在");
//        }else {
//            System.out.println("请输入你想修改的登录名：");
//            String wantLoginName = scanner.nextLine();
//            System.out.println("请输入你想修改的密码：");
//            String wantUserPwd = scanner.nextLine();
//            System.out.println("请输入你想修改的真实姓名：");
//            String wantRealName = scanner.nextLine();
//            ud.update(wantuserid,wantLoginName,wantUserPwd,wantRealName);
//        }

//
//        System.out.println("请输入你想删除的userid：");
//        Scanner scanner = new Scanner(System.in);
//        int deleteId = scanner.nextInt();
//        scanner.nextLine();
//        if (ud.select(1)==null){
//            System.out.println("该用户不存在");
//        }else {
//            ud.delete(deleteId);
//        }

        //ud.select();
        ud.login(3,"guanyu");
    }
}
